﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Sac.Data;
using System.Configuration;
using System.Data;

namespace Sac.Web
{
    public class UserRoleOperate
    {
        OledbPro oledb;

        public UserRoleOperate()
        {
            string ConnStr = ConfigurationManager.AppSettings["access"].ToString();
            oledb = new OledbPro(ConnStr);
        }

        /// <summary>
        /// 添加用户
        /// </summary>
        /// <param name="user">包含添加用户信息的对象</param>
        /// <returns>操作是否成功</returns>
        public bool InsertUser(UserRole user)
        {
            string sqlInsert = "insert into user_role(user_name,role_id_key,desc) values ('" + user.UserName + "',"
                    + user.RoleIdKey + ",'" + user.Desc + "')";
            int i = oledb.ExecuteNonQuery(sqlInsert);
            if (i == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public string Login(string userName, string password)
        {
            string sqlLogin = "select role_id_key,user_password from user_role where user_name='" + userName + "'";
            string userPassword = "";
            string roleId = "";
            try
            {
                userPassword = oledb.GetDataRow(sqlLogin)[1].ToString();
                roleId = oledb.GetDataRow(sqlLogin)[0].ToString();
            }
            catch
            {
                return "";
            }
            if (password == userPassword)
            {
                return roleId;
            }
            else
            {
                return "";
            }
        }

        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="userName">指定的用户名</param>
        /// <returns>操作是否成功</returns>
        public bool DeleteUserByName(string userName)
        {
            string sqlDelete = "delete from user_role where user_name='" + userName + "'";
            int i = oledb.ExecuteNonQuery(sqlDelete);
            if (i == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="idKey">用户ID号</param>
        /// <returns>操作是否成功</returns>
        public bool DeleteUserById(string idKey)
        {
            string sqlDelete = "delete from user_role where id_key=" + idKey;
            int i = oledb.ExecuteNonQuery(sqlDelete);
            if (i == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="userName">需要修改密码的用户名</param>
        /// <param name="userPassword">新的密码</param>
        /// <returns>操作是否成功</returns>
        public bool UpdatePasswordByUserName(string userName, string userPassword)
        {
            string UpdatePassword = "update user_role set USER_PASSWORD='" + userPassword + "' where USER_NAME='" + userName + "'";
            int i = oledb.ExecuteNonQuery(UpdatePassword);
            if (i == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 充值用户密码为888888
        /// </summary>
        /// <param name="userId">用户Id号</param>
        /// <returns>操作是否成功</returns>
        public bool ResetPassword(string userId)
        {
            string sqlResetPwd = "update user_role set USER_PASSWORD='888888' where id_key=" + userId;
            int i = oledb.ExecuteNonQuery(sqlResetPwd);
            if (i == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 检验密码
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <param name="userPassword">密码</param>
        /// <returns>是否存在该用户</returns>
        public bool CheckPassword(string userName, string userPassword)
        {
            string sqlGetPwd = "select user_password from user_role where user_name='" + userName + "'";
            string password = "";
            try
            {
                password = oledb.GetDataRow(sqlGetPwd)[0].ToString();
            }
            catch
            {
                return false;
            }
            if (password == userPassword)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 根据用户名获取用户所有信息
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <returns>包含用户信息的对象</returns>
        public UserRole GetUserByName(string userName)
        {
            UserRole selectUser = new UserRole();
            string GetUser = "select * from user_role where user_name='" + userName + "'";
            DataRow drUserInfo = oledb.GetDataRow(GetUser);
            if (drUserInfo != null)
            {
                selectUser.IdKey = Convert.ToInt32(drUserInfo["id_key"]);
                selectUser.UserName = drUserInfo["user_name"].ToString();
                selectUser.RoleIdKey = drUserInfo["role_id_key"].ToString();
                selectUser.UserPassword = drUserInfo["user_password"].ToString();
                selectUser.Desc = drUserInfo["desc"].ToString();
            }
            return selectUser;
        }

        /// <summary>
        /// 获取指定角色的所有用户
        /// </summary>
        /// <param name="roleName">指定角色名</param>
        /// <returns></returns>
        public DataTable GetUsersByRoleIdKey(string roleIdKey)
        {
            string GetUsers = "select * from user_role where role_id_key=" + roleIdKey;
            DataTable dtUserList = oledb.GetDataTable(GetUsers);
            return dtUserList;
        }

        /// <summary>
        /// 获取属于指定角色的用户数量
        /// </summary>
        /// <param name="roleName">角色名</param>
        /// <returns>用户数量</returns>
        public int GetUserCount(string roleIdKey)
        {
            string sqlUsersCount = "select count(*) from user_role where role_id_key=" + roleIdKey;
            int i = oledb.ExecuteScalar(sqlUsersCount);
            return i;
        }

        /// <summary>
        /// 获取所有用户
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllUsers()
        {
            string GetAllUsers = "select * from user_role";
            DataTable dtAllUser = oledb.GetDataTable(GetAllUsers);
            return dtAllUser;
        }
    }
}
